SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vw_OrdenDeMeritoGeneral
AS
SELECT DISTINCT 
                      CO.Descripcion AS Convocatoria, CO.idConvocatoria, N.NivelEnsenanza AS Nivel, IC.Nivel AS IdNivel, IC.Modalidad, m.Descripcion, 
                      CL.Descripcion AS Ciclo, IC.Ciclo AS IdCiclo, CG.idCargoGenerico AS IdCargo, CG.Descripcion AS Cargo, AG.idAsignaturaGenerica AS IdAsignatura, 
                      SUBSTRING(AG.Descripcion, 9, 150) AS Asignatura, C.Descripcion AS CategoriaDeTitulo, A.Documento, A.Nombres AS NombreYApellido, IC.Puntaje, 
                      A.Domicilio AS Direccion, L2.localidad AS ALocalidad, A.Telefono AS TE, CASE WHEN N.IdNivelEnsenanza IN (2) 
                      THEN A.LegajoDeInicial WHEN N.IdNivelEnsenanza IN (3) THEN A.LegajoDePrimaria WHEN N.IdNivelEnsenanza IN (4) 
                      THEN A.LegajoDeMedia END AS Legajo, IC.PromedioTitulo AS Promedio, IC.TituloEspecifico, IC.SituacionRevista, 
                      CASE WHEN N.IdNivelEnsenanza IN (2) THEN 1 WHEN N.IdNivelEnsenanza IN (4) THEN 2 WHEN N.IdNivelEnsenanza = 3 AND 
                      CL.IdCiclo = 1 THEN 1 WHEN N.IdNivelEnsenanza = 3 AND CL.IdCiclo = 2 THEN 2 ELSE 3 END AS TituloDelReporte, 
                      CASE WHEN ic.cargogenerico IN (35, 320, 405, 620) AND ic.titulovalorado = 71870 THEN '##' WHEN ic.cargogenerico IN (55, 415, 330) AND 
                      ic.titulovalorado = 46480 THEN '**' ELSE '  ' END AS tit, IC.CategoriaTituloAdultos, CG.Mnemo, dbo.tb_Organizaciones.Nombre AS EscuelaDeOrigen, 
                      IC.TituloValorado
FROM         dbo.tb_InscripcionesConvocatorias IC INNER JOIN
                      dbo.tb_CargosGenericos CG ON CG.idCargoGenerico = IC.CargoGenerico INNER JOIN
                      dbo.tb_AsignaturasGenericas AG ON AG.idAsignaturaGenerica = IC.AsignaturaGenerica INNER JOIN
                      dbo.tb_Agentes A ON A.idAgente = IC.Agente INNER JOIN
                      dbo.tb_Convocatorias CO ON CO.idConvocatoria = IC.Convocatoria INNER JOIN
                      dbo.tb_NivelesEnsenanza N ON N.idNivelEnsenanza = IC.Nivel INNER JOIN
                      dbo.tb_ModalidadesEnsenanza m ON m.idModalidad = IC.Modalidad INNER JOIN
                      dbo.tb_Ciclos CL ON CL.IdCiclo = IC.Ciclo LEFT OUTER JOIN
                      dbo.tb_Organizaciones ON IC.Organizacion = dbo.tb_Organizaciones.idOrganizaciones LEFT OUTER JOIN
                      dbo.tb_Localidades L2 ON L2.idLocalidad = A.Localidad LEFT OUTER JOIN
                      dbo.tb_CategoriasDeTitulo C ON C.idCategoria = IC.CategoriaDeTitulo
GO
GRANT SELECT ON  [dbo].[vw_OrdenDeMeritoGeneral] TO [Juntas]
GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[48] 4[26] 2[9] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1[50] 2[25] 3) )"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1 [56] 4 [18] 2))"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "IC"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 241
            End
            DisplayFlags = 280
            TopColumn = 13
         End
         Begin Table = "CG"
            Begin Extent = 
               Top = 6
               Left = 279
               Bottom = 114
               Right = 468
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AG"
            Begin Extent = 
               Top = 6
               Left = 506
               Bottom = 114
               Right = 695
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "A"
            Begin Extent = 
               Top = 6
               Left = 733
               Bottom = 114
               Right = 934
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CO"
            Begin Extent = 
               Top = 6
               Left = 972
               Bottom = 114
               Right = 1161
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "N"
            Begin Extent = 
               Top = 164
               Left = 73
               Bottom = 242
               Right = 262
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "m"
            Begin Extent = 
               Top = 114
               Left = 265
               Bottom = 207
               Right = 454
            End
            DisplayFlags = 280
            TopColumn = 0
         End
', 'USER', N'dbo', 'VIEW', N'vw_OrdenDeMeritoGeneral', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_DiagramPane2', N'         Begin Table = "CL"
            Begin Extent = 
               Top = 114
               Left = 492
               Bottom = 192
               Right = 681
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tb_Organizaciones"
            Begin Extent = 
               Top = 203
               Left = 390
               Bottom = 318
               Right = 580
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "L2"
            Begin Extent = 
               Top = 223
               Left = 956
               Bottom = 331
               Right = 1145
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "C"
            Begin Extent = 
               Top = 114
               Left = 719
               Bottom = 222
               Right = 908
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      RowHeights = 220
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'USER', N'dbo', 'VIEW', N'vw_OrdenDeMeritoGeneral', NULL, NULL
GO
DECLARE @xp int
SELECT @xp=2
EXEC sp_addextendedproperty N'MS_DiagramPaneCount', @xp, 'USER', N'dbo', 'VIEW', N'vw_OrdenDeMeritoGeneral', NULL, NULL
GO
